package com.ctfo.ulcp.exp.utils;

import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLExpr;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.ast.expr.SQLCharExpr;
import com.alibaba.druid.sql.ast.statement.SQLColumnDefinition;
import com.alibaba.druid.sql.ast.statement.SQLCreateTableStatement;
import com.alibaba.druid.sql.ast.statement.SQLTableElement;
import com.alibaba.druid.util.JdbcConstants;
import com.alibaba.druid.util.MySqlUtils;
import lombok.extern.slf4j.Slf4j;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Map;

@Slf4j
public class MySqlService extends JdbcService {

    public MySqlService(String url, String user, String password) throws SQLException {
        super(url, user, password);
    }

    @Override
    public List<TableInfo> fetchTableInfoList() throws SQLException {
        String sql = String.format("SELECT TABLE_NAME, TABLE_TYPE FROM information_schema.TABLES WHERE TABLE_SCHEMA = '%s'", schema);
        List<Map<String, Object>> rows = executeQuery(sql);
        List<TableInfo> tables = new ArrayList<>();
        for (Map<String, Object> row : rows) {
            String typeName = (String) row.get("TABLE_TYPE");
            String tableName = (String) row.get("TABLE_NAME");
            TableType tableType = "VIEW".equalsIgnoreCase(typeName) ? TableType.View : TableType.Table;
            TableInfo tableInfo;
            if (tableType == TableType.Table) {
                tableInfo = getTableInfo(tableName);
            } else {
                tableInfo = getViewInfoFromMetaData(tableName);
            }
            tables.add(tableInfo);
        }
        return tables;
    }

    @Override
    public Map<String, Object> fetchSampleData(String tableName) throws SQLException {
        List<Map<String, Object>> rows = executeQuery("SELECT * FROM " + tableName + " LIMIT 1");
        if (!rows.isEmpty()) {
            return rows.getFirst();
        }
        return null;
    }


    /**
     * 获取MySQL表或视图的结构
     * 此方法执行效率不如直接使用DDL。所以只针对视图使用
     * 注意: tableName 是视图的场合, 表注释固定为 'VIEW'
     */
    private TableInfo getViewInfoFromMetaData(String tableName) throws SQLException {
        String sql = String.format(
                "SELECT " +
                        " TABLES.TABLE_SCHEMA, " +
                        " TABLES.TABLE_NAME, " +
                        " TABLES.TABLE_COMMENT, " +
                        " COLUMNS.COLUMN_NAME, " +
                        " COLUMNS.COLUMN_TYPE, " +
                        " COLUMNS.COLUMN_COMMENT " +
                        "FROM information_schema.TABLES " +
                        "JOIN information_schema.COLUMNS ON TABLES.TABLE_SCHEMA = COLUMNS.TABLE_SCHEMA AND TABLES.TABLE_NAME = COLUMNS.TABLE_NAME " +
                        "WHERE TABLES.TABLE_SCHEMA = '%s' AND TABLES.TABLE_NAME = '%s' " +
                        "ORDER BY TABLES.TABLE_NAME, COLUMNS.ORDINAL_POSITION", schema, tableName
        );

        List<Map<String, Object>> tableColumnRows = executeQuery(sql);
        if (tableColumnRows.isEmpty()) {
            throw new IllegalArgumentException("no result found for arguments");
        }

        List<ColumnDefinition> columns = new ArrayList<>();
        for (Map<String, Object> tableColumnRow : tableColumnRows) {
            String name = (String) tableColumnRow.get("COLUMN_NAME");
            String comment = (String) tableColumnRow.get("COLUMN_COMMENT");
            // 注释中如果包含换行符,将其替换为空格
            if (comment != null) {
                comment = comment.replaceAll("\\r?\\n", " ");
            }
            String type = (String) tableColumnRow.get("COLUMN_TYPE");
            ColumnDefinition columnDefinition = new ColumnDefinition();
            columnDefinition.setName(name);
            columnDefinition.setComment(comment);
            columnDefinition.setType(type);
            columns.add(columnDefinition);
        }

        // tableName 是视图的场合, 表注释固定为 'VIEW'
        String tableComment = (String) tableColumnRows.getFirst().get("TABLE_COMMENT");
        return new TableInfo(tableName, tableComment, columns, TableType.View);
    }


    private TableInfo getTableInfo(String tableName) throws SQLException {
        String ddl = getTableDDL(tableName);
        List<SQLStatement> sqlStatements = SQLUtils.parseStatements(ddl, JdbcConstants.MYSQL);
        SQLCreateTableStatement createTableStmt = (SQLCreateTableStatement) sqlStatements.getFirst();
        createTableStmt.simplify();


        List<ColumnDefinition> columns = new ArrayList<>();
        for (SQLTableElement element : createTableStmt.getTableElementList()) {
            if (element instanceof SQLColumnDefinition columnDef) {
                String name = columnDef.getColumnName();
                // 某些名称, 如 "type", 会被反引号包裹
                name = removeSurroundingBackticks(name);

                String comment = getCommentText(columnDef.getComment());

                // 注释中如果包含换行符,将其替换为空格
                if (comment != null) {
                    comment = comment.replaceAll("\\r?\\n", " ");
                }


                String type = columnDef.getDataType().getName();

                ColumnDefinition columnDefinition = new ColumnDefinition();
                columnDefinition.setName(name);
                columnDefinition.setComment(comment);
                columnDefinition.setType(type);
                columns.add(columnDefinition);
            }
        }

        String tableComment = getCommentText(createTableStmt.getComment());
        return new TableInfo(tableName,  tableComment,columns, TableType.Table);
    }

    private String getTableDDL(String tableName) throws SQLException {
        log.debug("getTableDDL:{}", tableName);
        List<String> ddlList = MySqlUtils.getTableDDL(connection, Collections.singletonList(tableName));
        return ddlList.getFirst();
    }

    private static String getCommentText(SQLExpr comment) {
        if (comment != null) {
            if (comment instanceof SQLCharExpr) {
                return ((SQLCharExpr) comment).getText();
            }
        }
        return null;
    }

    /**
     * 去掉字符串两端可能存在的反引号
     */
    private static String removeSurroundingBackticks(String input) {
        if (input == null || input.length() < 2) {
            return input;
        }

        if (input.charAt(0) == '`' && input.charAt(input.length() - 1) == '`') {
            return input.substring(1, input.length() - 1);
        }

        return input;
    }


}
